﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class LichLamViecDAO
    {
        public static List<LichLamViecDTO> Select_by_CaLamViec_Ngay(int MaCaLamViec, string Ngay)
        {
            List<LichLamViecDTO> list = new List<LichLamViecDTO>();
            String strSql = "select MaLLV,HoTen,TenKhuVuc from LichLamViec llv,NhanVien nv,KhuVuc kv,CaLamViec clv where nv.MaNV=llv.NhanVien " +
                "and CaLamViec=" + MaCaLamViec + " and Ngay='" + Ngay + "' and llv.CaLamViec=clv.MaCa and llv.KhuVuc=kv.MaKhuVuc";

            try
            {
                DataTable dt = SqlDataAccessHelper.ExcuteQuery(strSql);
                foreach (DataRow dr in dt.Rows)
                {
                    LichLamViecDTO llv = new LichLamViecDTO();
                    llv.MaLLV = int.Parse(dr["MaLLv"].ToString());
                    llv.TenNhanVien= dr["HoTen"].ToString();
                    llv.TenKhuVuc= dr["TenKhuVuc"].ToString();
                    list.Add(llv);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static List<LichLamViecDTO> Select_by_TenNhanVien(string TenNhanVien)
        {
            List<LichLamViecDTO> list = new List<LichLamViecDTO>();
            String strSql = "select * from LichLamViec llv,NhanVien nv,KhuVuc kv,CaLamViec clv where nv.MaNV=llv.NhanVien " +
                "and llv.CaLamViec=clv.MaCa and llv.KhuVuc=kv.MaKhuVuc and HoTen like N'%" + TenNhanVien + "%'";

            try
            {
                DataTable dt = SqlDataAccessHelper.ExcuteQuery(strSql);
                foreach (DataRow dr in dt.Rows)
                {
                    LichLamViecDTO llv = new LichLamViecDTO();
                    llv.MaLLV = int.Parse(dr["MaLLV"].ToString());
                    llv.TenNhanVien = dr["HoTen"].ToString();
                    llv.MaNhanVien = int.Parse(dr["MaNV"].ToString());
                    llv.TenKhuVuc = dr["TenKhuVuc"].ToString();
                    llv.TenCa = dr["TenCa"].ToString();
                    llv.MaCa = int.Parse(dr["MaCa"].ToString());
                    llv.Ngay = DateTime.Parse(dr["Ngay"].ToString()).ToString("MM/dd/yyyy");
                    list.Add(llv);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
      
        public static List<LichLamViecDTO> Select_NhanVien_not_exists_in_CaLamViec_Ngay(int MaCaLamViec,string Ngay)
        {
            List<LichLamViecDTO> list = new List<LichLamViecDTO>();
            String strSql = "select MaNV,HoTen from NhanVien where MaNV not in " +
                "(select NhanVien from LichLamViec llv,NhanVien nv where nv.MaNV=llv.NhanVien and CaLamViec=" + MaCaLamViec + " and Ngay='" + Ngay + "') " +
                "and MaNV not in(select NhanVien from NghiPhep where NgayBatDauNghi<='" + Ngay + "' and DATEADD(Day,SoNgayNghi,NgayBatDauNghi)>='" + Ngay + "')";

            try
            {
                DataTable dt = SqlDataAccessHelper.ExcuteQuery(strSql);
                foreach (DataRow dr in dt.Rows)
                {
                    LichLamViecDTO llv = new LichLamViecDTO();
                    llv.MaNhanVien = int.Parse(dr["MaNV"].ToString());
                    llv.TenNhanVien = dr["HoTen"].ToString();
                    list.Add(llv);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static bool Insert(LichLamViecDTO llv)
        {
            bool result = false;

            string strSQLEmployee = "Insert into LichLamViec " +
                "values (@Nhan,@Ca,@Khu,@Ngay)";

            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@Nhan", llv.MaNhanVien));
                sqlParams.Add(new SqlParameter("@Ca", llv.MaCa));
                sqlParams.Add(new SqlParameter("@Khu", llv.MaKhuVuc));
                sqlParams.Add(new SqlParameter("@Ngay", llv.Ngay));
                // Call execute none query

                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);//.ExecuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool Update(LichLamViecDTO llv)
        {
            bool result = false;

            string strSQLEmployee = "Update LichLamViec set NhanVien=@Nhan where MaLLV=@ID";

            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@ID", llv.MaLLV));
                sqlParams.Add(new SqlParameter("@Nhan", llv.MaNhanVien));
                // Call execute none query

                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);//.ExecuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool Delete(int MaLLV)
        {
            bool result = false;
            string strSQLEmployee = "DELETE FROM LichLamViec WHERE MaLLV=@ID";
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@ID", MaLLV));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static List<LichLamViecDTO> KiemTraNgayDoNhanVienCoLamHayKhong(string Ngay,int MaNhanVien)
        {
            List<LichLamViecDTO> list = new List<LichLamViecDTO>();
            String strSql = "select * from LichLamViec llv,NhanVien nv,KhuVuc kv,CaLamViec clv where nv.MaNV=llv.NhanVien " +
                "and llv.CaLamViec=clv.MaCa and llv.KhuVuc=kv.MaKhuVuc and Ngay='" + Ngay + "' and NhanVien=" + MaNhanVien;

            try
            {
                DataTable dt = SqlDataAccessHelper.ExcuteQuery(strSql);
                foreach (DataRow dr in dt.Rows)
                {
                    LichLamViecDTO llv = new LichLamViecDTO();
                    llv.MaLLV = int.Parse(dr["MaLLV"].ToString());
                    llv.TenNhanVien = dr["HoTen"].ToString();
                    llv.MaNhanVien = int.Parse(dr["MaNV"].ToString());
                    llv.TenKhuVuc = dr["TenKhuVuc"].ToString();
                    llv.TenCa = dr["TenCa"].ToString();
                    llv.MaCa = int.Parse(dr["MaCa"].ToString());
                    llv.Ngay = DateTime.Parse(dr["Ngay"].ToString()).ToString("MM/dd/yyyy");
                    list.Add(llv);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
    }
}
